RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / Repositories /
Filename : RewardRepository.php
back
<?php
namespace App\V2\Repositories;

use App\Models\Product;
use App\V2\Dtos\FilterRewardDTO;
use App\V2\Dtos\SearchDTO;
use App\V2\Resources\RewardResource;
use App\V2\Services\RewardService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class RewardRepository extends BaseRepository {

    public static $PRODUCT_TABLE_NAME = "products";
    public static $STORE_PRODUCT_TABLE_NAME = "store_product";
    public static $REWARD_SETTING_TABLE_NAME = "reward_settings";
    public static $REWARD_VISIBILITY_TABLE_NAME = "reward_visibility";
    public static $REWARD_VISIBILITY_SINGLE_COUNTRY_ALIAS = "visibility_country_single";
    public static $REWARD_VISIBILITY_SINGLE_COUNTRY_TABLE_NAME = "reward_visibility AS visibility_country_single";


    public function search(SearchDTO $searchDto){
        $queryBuilder = Product::select( RewardRepository::$PRODUCT_TABLE_NAME .'.*');

        $queryBuilder->leftJoin(RewardRepository::$STORE_PRODUCT_TABLE_NAME,
            function ($join) use($searchDto) {
                $join->on(RewardRepository::$STORE_PRODUCT_TABLE_NAME.'.product_id',
                        '=', RewardRepository::$PRODUCT_TABLE_NAME .'.product_id');
                $join->on(RewardRepository::$STORE_PRODUCT_TABLE_NAME . '.deleted_at',
                        'IS', DB::raw('NULL'));
            }
        );

        //left join with settings
        $queryBuilder->leftJoin(RewardRepository::$REWARD_SETTING_TABLE_NAME,
            function($join) use($searchDto){
                $join->on(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.reward_id',
                    '=', RewardRepository::$PRODUCT_TABLE_NAME .'.product_id');
            }
        );

        //left join with reward visibility
        $queryBuilder->leftJoin(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME,
            function($join) use($searchDto){
                $join->on(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.reward_settings_id',
                    '=', RewardRepository::$REWARD_SETTING_TABLE_NAME .'.reward_settings_id');
            }
        );
        $queryBuilder->leftJoin(RewardRepository::$REWARD_VISIBILITY_SINGLE_COUNTRY_TABLE_NAME,
            function($join) {
                $join->on(RewardRepository::$REWARD_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.reward_settings_id',
                    '=', RewardRepository::$REWARD_VISIBILITY_SINGLE_COUNTRY_ALIAS .'.reward_settings_id');
                $join->on(RewardRepository::$REWARD_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_type', '=' ,DB::raw("'COUNTRY_SINGLE'"));
            }
        );

        

        if($searchDto->getRequiredFor() == 'PARENT'){
            $queryBuilder->where(function ($query) use($searchDto) {
                // Below condition for global_for_parent 
                $query->orWhereIN(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                    array("GLOBAL_FOR_PARENT"));

                    // below condition for selected parent 
                $query->orWhere(function ($query) use($searchDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SELECTED_PARENT") );
                    $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.entity_type', 'USER');
                    $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.entity_value', $searchDto->getCurrentUser()->user_id);
                });
                // below condition for country group
                $group_country_name = $searchDto->getCurrentGroup()->country != null?
                    $searchDto->getCurrentGroup()->country->country_name : null;

                if($group_country_name != null){
                    $query->orWhere(function ($query) use($searchDto,$group_country_name){
                        $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                            array("COUNTRY_GROUP") );
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_type',
                            '=', 'COUNTRY');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_value',
                            '=', $group_country_name);
                    });
                }
                // below condition single group
                $query->orWhere(function ($query) use($searchDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SINGLE_GROUP") );
                    $query->where(RewardRepository::$PRODUCT_TABLE_NAME .'.group_id',
                        '=', $searchDto->getCurrentGroupId());
                });
            });
        }else if($searchDto->getRequiredFor() == 'CHILD'){
            $queryBuilder->where(function ($query) use($searchDto) {
                // Below condition is for single group //
                $query->orWhere(function ($query) use($searchDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SINGLE_GROUP") );
                    $query->where(RewardRepository::$PRODUCT_TABLE_NAME .'.group_id',
                        '=', $searchDto->getCurrentGroupId());
                });

                // Below condition is for country group //
                $group_country_name = $searchDto->getCurrentGroup()->country != null?
                    $searchDto->getCurrentGroup()->country->country_name : null;

                if($group_country_name != null){
                    $query->orWhere(function ($query) use($searchDto, $group_country_name){
                        $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                            array("COUNTRY_GROUP") );
                        $query->where(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_in_family_store',
                            '!=', '0');
                        $query->orWhereNull(RewardRepository::$REWARD_SETTING_TABLE_NAME . '.visible_in_family_store');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_type',
                            '=', 'COUNTRY');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_value',
                            '=', $group_country_name);
                    });
                }    
            });
            $queryBuilder->whereNot(RewardRepository::$STORE_PRODUCT_TABLE_NAME.'.is_published', '<=>', 0);
        }

        $queryBuilder->where(function ($query) use($searchDto) {
            $query->where(RewardRepository::$PRODUCT_TABLE_NAME.'.product_name', 'LIKE', DB::raw("'%".$searchDto->getQ()."%'"))
            ->orWhere(RewardRepository::$PRODUCT_TABLE_NAME.'.description', 'LIKE', DB::raw("'%".$searchDto->getQ()."%'"));
        });

        $paginatedRewards = $queryBuilder->groupBy(RewardRepository::$PRODUCT_TABLE_NAME.'.product_id')
                   ->paginate($searchDto->getLimit(), ['*'], 'page', $searchDto->getPageNo());
        return $paginatedRewards;
    }

    public function getRewardsNew(FilterRewardDTO $filterDto){

        $queryBuilder = Product::select( RewardRepository::$PRODUCT_TABLE_NAME .'.*');

        //returning reward for child if the product is added in the current store.
        $queryBuilder->leftJoin(RewardRepository::$STORE_PRODUCT_TABLE_NAME,
        function ($join) use($filterDto) {
            $join->on(RewardRepository::$STORE_PRODUCT_TABLE_NAME.'.product_id',
                    '=', RewardRepository::$PRODUCT_TABLE_NAME .'.product_id');
            $join->on(RewardRepository::$STORE_PRODUCT_TABLE_NAME . '.deleted_at',
                'IS', DB::raw('NULL'));
        });

        //left join with settings
        $queryBuilder->leftJoin(RewardRepository::$REWARD_SETTING_TABLE_NAME,
            function($join) use($filterDto){
                $join->on(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.reward_id',
                    '=', RewardRepository::$PRODUCT_TABLE_NAME .'.product_id');
            }
        );

        //left join with reward visibility
        $queryBuilder->leftJoin(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME,
            function($join) use($filterDto){
                $join->on(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.reward_settings_id',
                    '=', RewardRepository::$REWARD_SETTING_TABLE_NAME .'.reward_settings_id');
            }
        );

        Log::info("child if condition before " .$filterDto->getRequiredFor());
        if($filterDto->getRequiredFor() == 'CHILD'){
            Log::info("child if condition after");
            $queryBuilder->where(function ($query) use($filterDto) {
                // Below condition for global_for_parent 
                $query->orWhere(function ($query) use($filterDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("GLOBAL_FOR_PARENT") );
                    $query->where(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_in_family_store',
                        '=', 1);
                    $query->where(RewardRepository::$STORE_PRODUCT_TABLE_NAME.'.store_id',
                        '=', $filterDto->getCurrentStoreId());
                    $query->where(RewardRepository::$STORE_PRODUCT_TABLE_NAME.'.is_published',
                        '=', 1);
                });

                // Below condition is for single group //
                $query->orWhere(function ($query) use($filterDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SINGLE_GROUP") );
                    $query->where(RewardRepository::$PRODUCT_TABLE_NAME .'.group_id',
                        '=', $filterDto->getCurrentGroupId());
                });

                // Below condition is for country group //
                $group_country_name = $filterDto->getCurrentGroup()->country != null?
                    $filterDto->getCurrentGroup()->country->country_name : null;

                if($group_country_name != null){
                    $query->orWhere(function ($query) use($filterDto, $group_country_name){
                        $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                            array("COUNTRY_GROUP") );
                        $query->where(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_in_family_store',
                            '!=', '0');
                        $query->orWhereNull(RewardRepository::$REWARD_SETTING_TABLE_NAME . '.visible_in_family_store');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_type',
                            '=', 'COUNTRY');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_value',
                            '=', $group_country_name);
                    });
                }  
                
                $query->orWhere(function ($query) use ($filterDto) {
                    $query->where(RewardRepository::$STORE_PRODUCT_TABLE_NAME . '.store_id', '=', $filterDto->getCurrentStoreId());
                    $query->where(RewardRepository::$STORE_PRODUCT_TABLE_NAME . '.is_published', '<=>', 0);
                });
            });
        } else if($filterDto->getRequiredFor() == 'PARENT'){
            
            $queryBuilder->where(function ($query) use($filterDto) {
                // Below condition for global_for_parent 
                $query->orWhereIN(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                    array("GLOBAL_FOR_PARENT"));

                // Below condition for selected parent 
                $query->orWhere(function ($query) use($filterDto){
                    Log::debug("Checked for GLOBAL_FOR_PARENT");
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SELECTED_PARENT") );
                    $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.entity_type', 'USER');
                    $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME.'.entity_value', $filterDto->getCurrentUser()->user_id);
                });

                // below condition for country group
                $group_country_name = $filterDto->getCurrentGroup()->country != null?
                    $filterDto->getCurrentGroup()->country->country_name : null;

                if($group_country_name != null){
                    $query->orWhere(function ($query) use($filterDto,$group_country_name){
                        $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                            array("COUNTRY_GROUP") );
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_type',
                            '=', 'COUNTRY');
                        $query->where(RewardRepository::$REWARD_VISIBILITY_TABLE_NAME .'.entity_value',
                            '=', $group_country_name);
                    });
                }
                // below condition single group
                $query->orWhere(function ($query) use($filterDto){
                    $query->whereIn(RewardRepository::$REWARD_SETTING_TABLE_NAME.'.visible_to',
                        array("SINGLE_GROUP") );
                    $query->where(RewardRepository::$PRODUCT_TABLE_NAME .'.group_id',
                        '=', $filterDto->getCurrentGroupId());
                });
            });
        }

        //Sorting and ordering
        $order_by_column = RewardRepository::$PRODUCT_TABLE_NAME.'.product_id';
        $order = 'DESC';

        if($filterDto->getOrder() != null){
            $order = $filterDto->getOrder();
        }

        if($filterDto->getOrderByColumn() != null){
            $order_by_column = RewardRepository::$PRODUCT_TABLE_NAME.".".$filterDto->getOrderByColumn();
        }

        $queryBuilder->orderBy($order_by_column, $order);
        $queryBuilder->groupBy(RewardRepository::$PRODUCT_TABLE_NAME.'.product_id');

        //limits and pagination
        if($filterDto->getLimit() != null)
            $queryBuilder->limit($filterDto->getLimit());

        Log::debug("SQL to get Rewards: ". $queryBuilder->toSql());
        Log::debug("Values for Sql:" . print_r($queryBuilder->getBindings(), true));
        if($filterDto->getOnlyCount() != null && $filterDto->getOnlyCount() == true)
            return $queryBuilder->count();
        else if($filterDto->getPageNo() != null)
            return $queryBuilder->paginate($filterDto->getLimit(), ['*'], 'page', $filterDto->getPageNo());
        else
            return $queryBuilder->get();
    }
    
    
    


}
?>